package DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Vector;

import DataObjects.Menu;
import DataObjects.Recipe;

public class GetMenuTableDetails {
	
	private Vector<Vector> menuTable;
	Vector<Menu> menues;
	Vector<Recipe> recipes;
	
	public Vector<Menu> getMenuTable()
	{
		SQLConnection sc=new SQLConnection();
		menuTable = new Vector<Vector>();
		menues=new Vector<Menu>();
		recipes=new Vector<Recipe>();
		
		try {
			PreparedStatement ps1 = sc.getConnection().prepareStatement("SELECT M.mid, M.Mname, M.Mprice FROM menu M  WHERE M.Mstatus='פעיל' AND M.mtype='business';");
			PreparedStatement ps2 = sc.getConnection().prepareStatement("SELECT R.Rid,R.Rname,M.mprice,RM.role,RM.isSelectable FROM  recipe_in_menu RM, recipe R ,menu M " +
					                                                     "WHERE  M.mid=? AND M.mid=RM.mid AND RM.Rid=R.Rid AND R.Rstatus='פעיל'   ;");
			PreparedStatement ps3 = sc.getConnection().prepareStatement("SELECT M.mid, M.Mname FROM menu M WHERE M.mtype='simple' ;");
			PreparedStatement ps4 = sc.getConnection().prepareStatement("SELECT R.Rid,R.Rname,RM.price FROM  recipe_in_menu RM, recipe R, menu M WHERE M.mid=? AND M.mid=RM.mid AND RM.Rid=R.Rid AND R.Rstatus='פעיל';");
			
			
			ResultSet rs1=ps1.executeQuery();
			while(rs1.next())
			{
				Menu menu=new Menu();
				menu.setType("business");
				menu.setId(rs1.getString(1));
				menu.setName(rs1.getString(2));
				menu.setPrice(rs1.getString(3));
				ps2.setString(1,rs1.getString(1));
				ResultSet rs2=ps2.executeQuery();
				Vector<Recipe> recipesOfBisnessMenu=new Vector<Recipe>();
				while(rs2.next())
				{
					Recipe rec=new Recipe();
					rec.setId(rs2.getString(1));
					rec.setName(rs2.getString(2));
					rec.setPrice(rs2.getString(3));
					rec.setRole(rs2.getString(4));
					rec.setIsSelectable(rs2.getInt(5));
					recipesOfBisnessMenu.add(rec);				
				}
				
				menu.setRecipes(recipesOfBisnessMenu);
				menues.add(menu);
				rs2.close();
			}
			
			ps1.close();
			ps2.close();
			rs1.close();
			
			
			ResultSet rs3=ps3.executeQuery();
			while(rs3.next())
			{
				Menu menu=new Menu();
				menu.setType("simple");
				menu.setId(rs3.getString(1));
				menu.setName(rs3.getString(2));
				menu.setPrice("0");
				ps4.setString(1,rs3.getString(1));
				ResultSet rs4=ps4.executeQuery();
				Vector<Recipe> recipesOfSimpleMenu=new Vector<Recipe>();
				while(rs4.next())
				{
					Recipe rec=new Recipe();
					rec.setId(rs4.getString(1));
					rec.setName(rs4.getString(2));
					rec.setPrice(rs4.getString(3));
					rec.setRole("0");
					int t=1;
					rec.setIsSelectable(t);
					recipesOfSimpleMenu.add(rec);				
				}
				
				menu.setRecipes(recipesOfSimpleMenu);
				menues.add(menu);
				rs4.close();
			}
			
			ps3.close();
			ps4.close();
			rs3.close();
			sc.getConnection().close();
			
					
			
			
	}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}
		return menues;

}
}
